package dao.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import db.common.DBHelper;
import model.common.LoggingNewGoods;
import model.common.SellSituation;

public class SellSituationDao {

	public void addSellSituation(SellSituation s) throws SQLException {
		Connection conn = DBHelper.getConnection();
		String sql = " "+"insert into tab_sellsituation " + "(goodsName,goodsUnitPrice,goodsPrice,goodsSellSum,"
				    + " sellTotalNum,goodsManufacturer,sellingId,sellDetails,recorder,recordDate ) "
				    + " values(?,?,?,?,?,?,?,?,?,?)  ";
		
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, s.getGoodsName());
		ptmt.setDouble(2, s.getGoodsUnitPrice());
		ptmt.setDouble(3, s.getGoodsPrice());
		ptmt.setDouble(4, s.getGoodsSellSum());
		ptmt.setInt(5, s.getSellTotalNum());
		ptmt.setString(6, s.getGoodsManufacturer());
		ptmt.setInt(7, s.getSellingId());
		ptmt.setString(8, s.getSellDetails());
		ptmt.setString(9, s.getRecorder());
		ptmt.setDate(10, new java.sql.Date(s.getRecordDate().getTime()));
		
		
		ptmt.execute();
		/*ptmt.close();
		conn.close();*/
	}
	
	public void deleteSellSituation(Integer goodsId) throws SQLException {
		Connection conn = DBHelper.getConnection();   
		String sql = " delete from tab_sellsituation  where goodsId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, goodsId);
		ptmt.execute();
		/*ptmt.close();
		conn.close();*/
	}

	public void updateSellSituation(SellSituation s) throws SQLException{
		Connection conn=DBHelper.getConnection();
		String sql=" update tab_sellsituation set goodsName=?,goodsUnitPrice=?,goodsPrice=?,"
		+ " goodsSellSum=?,sellTotalNum=?,goodsManufacturer=?,sellingId=?,sellDetails=?,recorder=?,recordDate=?"
				+ " where goodsId=? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		
		ptmt.setString(1, s.getGoodsName());
		ptmt.setDouble(2, s.getGoodsUnitPrice());
		ptmt.setDouble(3, s.getGoodsPrice());
		ptmt.setDouble(4, s.getGoodsSellSum());
		ptmt.setInt(5, s.getSellTotalNum());;
		ptmt.setString(6, s.getGoodsManufacturer());
		ptmt.setInt(7, s.getSellingId());
		ptmt.setString(8, s.getSellDetails());
		ptmt.setString(9, s.getRecorder());
		ptmt.setDate(10, new java.sql.Date(s.getRecordDate().getTime()));
		ptmt.setInt(11, s.getGoodsId());
		
		ptmt.execute();
	/*	ptmt.close();
		conn.close();*/
	}
	
	public SellSituation get(Integer goodsId) throws SQLException {
		SellSituation s = null;
		Connection conn = DBHelper.getConnection();
		String sql = " select * from tab_sellsituation where goodsId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, goodsId);
		ResultSet rs = ptmt.executeQuery();
		while (rs.next()) {
			s = new SellSituation();
			s.setGoodsId(rs.getInt("goodsStockId"));
			s.setGoodsName(rs.getString("goodsName"));
			s.setGoodsUnitPrice(rs.getDouble("goodsUnitPrice"));
			s.setGoodsPrice(rs.getDouble("goodsPrice"));
			s.setGoodsSellSum(rs.getDouble("goodsSellSum"));
			s.setSellTotalNum(rs.getInt("sellTotalNum"));
			s.setGoodsManufacturer(rs.getString("goodsManufacturer"));
			s.setSellingId(rs.getInt("sellingId"));
			s.setSellDetails(rs.getString("sellDetails"));		
			s.setRecorder(rs.getString("recorder"));
			s.setRecordDate(rs.getDate("recordDate"));
		}
		
		rs.close();
		ptmt.close();
		conn.close();
		return s;
		
	}
	
	public SellSituation search(Integer sellingId) throws SQLException {
		SellSituation s = null;
		Connection conn = DBHelper.getConnection();
		String sql = " select * from tab_sellsituation where sellingId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, sellingId);
		ResultSet rs = ptmt.executeQuery();
		while (rs.next()) {
			s = new SellSituation();
			s.setGoodsId(rs.getInt("goodsStockId"));
			s.setGoodsName(rs.getString("goodsName"));
			s.setGoodsUnitPrice(rs.getDouble("goodsUnitPrice"));
			s.setGoodsPrice(rs.getDouble("goodsPrice"));
			s.setGoodsSellSum(rs.getDouble("goodsSellSum"));
			s.setSellTotalNum(rs.getInt("sellTotalNum"));
			s.setGoodsManufacturer(rs.getString("goodsManufacturer"));
			s.setSellingId(rs.getInt("sellingId"));
			s.setSellDetails(rs.getString("sellDetails"));		
			s.setRecorder(rs.getString("recorder"));
			s.setRecordDate(rs.getDate("recordDate"));
		}
		
		rs.close();
		ptmt.close();
		conn.close();
		return s;
		
	}
	
	public List<SellSituation> query(List<Map<String, Object>> params) throws SQLException{
		List<SellSituation> result=new ArrayList<SellSituation>(); 
		Connection conn = DBHelper.getConnection();
		StringBuffer sb=new StringBuffer();
		sb.append("select * from tab_sellsituation where 1=1 ");
		if(params!=null&&params.size()>0){
			for(int i=0;i<params.size();i++){
				Map<String, Object> map=params.get(i);
				sb.append(" and " +map.get("name")+"  " +map.get("rela")+"  " +map.get("value"));
			}
		}
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());

		System.out.println(sb.toString());
		ResultSet rs=ptmt.executeQuery();
		
		SellSituation s = null;
		while (rs.next()) {
		s = new SellSituation();
		s.setGoodsId(rs.getInt("goodsId"));
		s.setGoodsName(rs.getString("goodsName"));
		s.setGoodsUnitPrice(rs.getDouble("goodsUnitPrice"));
		s.setGoodsPrice(rs.getDouble("goodsPrice"));
		s.setGoodsSellSum(rs.getDouble("goodsSellSum"));
		s.setSellTotalNum(rs.getInt("sellTotalNum"));
		s.setGoodsManufacturer(rs.getString("goodsManufacturer"));
		s.setSellingId(rs.getInt("sellingId"));
		s.setSellDetails(rs.getString("sellDetails"));
		s.setRecorder(rs.getString("recorder"));
		s.setRecordDate(rs.getDate("recordDate"));
			result.add(s);
		}
		/*rs.close();
		ptmt.close();
		conn.close();*/
		return result;
		}
}
	
	
